﻿Option Strict On

Imports KUeLib.Kategorien
Imports DbDataLib
Imports System.Data.Common
Imports System.Data

Namespace Services

    Public Class KategorienDataService
        Inherits ServiceDefinitions.KategorienDataService

        Public Sub New(ByVal dataService As DbDataService)
            MyBase.New(dataService)
        End Sub

        Protected Function GetLastInsertId() As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("SELECT last_insert_id()")

            cmd.CommandText = sb.ToString()

            Return CInt(_dataService.ExecuteScalar(cmd))
        End Function

        Public Overrides Function GetKategorieById(ByVal id As Integer) As Kategorie

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder
            Dim dt As New DataTable
            Dim k As Kategorie = Nothing

            sb.AppendLine("SELECT * FROM kategorien WHERE k_id = @p_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, id))

            dt = _dataService.GetDataTable(cmd)

            If dt.Rows.Count > 0 Then

                k = New Kategorie

                k.Id = CInt(Utils.DbNull2Nothing(dt.Rows(0)!k_id))
                k.Bezeichnung = CStr(Utils.DbNull2Nothing(dt.Rows(0)!k_bezeichnung))
                If Utils.DbNull2Nothing(dt.Rows(0)!k_parent) IsNot Nothing Then _
                    k.Parent = GetKategorieById(CInt(Utils.DbNull2Nothing(dt.Rows(0)!k_parent)))


            End If

            Return k

        End Function

        Public Overrides Function GetAlleKategorien() As List(Of Kategorie)

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder
            Dim dt As New DataTable
            Dim lok As New List(Of Kategorie)

            sb.AppendLine("SELECT * FROM kategorien ORDER BY k_bezeichnung")

            cmd.CommandText = sb.ToString()

            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1

                Dim k As New Kategorie

                k.Id = CInt(Utils.DbNull2Nothing(dt.Rows(i)!k_id))
                k.Bezeichnung = CStr(Utils.DbNull2Nothing(dt.Rows(i)!k_bezeichnung))
                If Utils.DbNull2Nothing(dt.Rows(i)!k_parent) IsNot Nothing Then _
                    k.Parent = GetKategorieById(CInt(Utils.DbNull2Nothing(dt.Rows(i)!k_parent)))

                lok.Add(k)

            Next

            Return lok
        End Function

        Public Overrides Function InsertKategorie(ByVal k As Kategorie) As Kategorie

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("INSERT INTO kategorien(k_bezeichnung) VALUES(@p_bezeichnung)")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_bezeichnung", DbType.String, k.Bezeichnung))

            _dataService.ExecuteNonQuery(cmd)

            k.Id = GetLastInsertId()

            Return k

        End Function

        Public Overrides Sub UpdateKategorie(ByVal k As Kategorie)

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("UPDATE kategorien SET k_bezeichnung = @p_bezeichnung WHERE k_id = @p_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, k.Id))
            cmd.Parameters.Add(_dataService.CreateParameter("p_bezeichnung", DbType.String, k.Bezeichnung))

            _dataService.ExecuteNonQuery(cmd)

        End Sub

       
    End Class



End Namespace